// (网络理政区县）查询本单位信件工单导出
def netMailWFListExport(Object parameter){
    def sql = '''
       SELECT m1.PkId,m1.WfId,m2.FmContent,m1.MailPK,m2.WorkFormNO,m2.FromTopic,m2.FromName,m2.FromTel,
       m3.SpareString,m3.FmAddress,m3.FromTime,m3.DFmClaName,SBDeptName,BackDesc,
       (select SortName from bSort where CodePk=str(left(m3.DCntACode, 4), 4)) dCntAName1,
       (select SortName from bSort where CodePk=str(left(m3.DCntACode, 6), 6)) dCntAName2,
       (select SortName from bSort where CodePk=str(left(m3.DCntACode, 8), 8)) dCntAName3,
       iif(m4.JudgeC='',0,m4.JudgeC) JudgeC,iif(m4.DeptPos='',0,m4.DeptPos) DeptPos,m1.GetTime signTime,
       SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit,
       BackTime,BackState,BackRemark,
       case when m1.SendState in(10,11) then '0' 
       when m1.SendState in(21,23) and m1.BackState in(0,12,22) then '2' 
       when m1.BackState = 10 then '3' when m1.SendState  = 40 then '4' 
       when m1.SendState not in(30,40) and m1.BackState in(11,21) then '10' 
       when m1.SendState in (22,23) then '1' else '-1' end as currentState 
       FROM mWfSendBack m1 INNER JOIN mMailWorkForm m2 on m1.WfId=m2.PkId 
       inner join mMailGetDeal m3 on m2.OriginFK=m3.PkId left join mWfJudge m4 on m4.WfId = m1.WfId 
       where m3.FmCerNO= #{param1} AND SendState IN(21,22,23,40) AND m1.IsDel=0 AND m2.IsDel=0 
    '''
        if (parameter?.workFormNo)
            sql += " and m2.WorkFormNO = #{workFormNO} "
        if (parameter?.wsCode)
            sql += " and m3.WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),m2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),m2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " and m2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and m2.FromTopic = #{fromTopic} "
        if(parameter?.sdeptName)
            sql += " and m1.sdeptName = #{sdeptName} "
    return sql
}

// 查询本单位电话工单导出
def telWFDeptListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel, 
        '12345' SpareString,t3.FmAddress,t3.FromTime, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        t3.DFmClaName,SBDeptName,BackDesc,t1.GetTime signTime, 
        case when t4.JudgeC='' then 0 else t4.JudgeC end as JudgeC, 
        case when t4.DeptPos='' then 0 else t4.DeptPos end as DeptPos, 
        t1.SDeptName,t1.SeqName,t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState AS BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM tWfSendBack t1 inner join tWorkFormBase t2 on t1.WfId=t2.PkId 
        inner join tTelGetDeal t3 on t2.GetDealPK=t3.PkId left join tWfJudge t4 on t4.WfId = t1.WfId 
        WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 and t2.IsDel=0 
'''
        if(parameter?.workFormNO)
            sql += " and t2.workFormNO = #{workFormNO} "
        if(parameter?.wsCode)
            sql += " and t2.WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " and t2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and t2.WsTopic = #{fromTopic} "
        if(parameter?.sdeptName)
            sql += " and t1.sdeptName = #{sdeptName} "
    return sql
}

// 查询下级单位电话工单导出
def subDeptTelWFListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel, 
        '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        t1.SDeptName,t1.SeqName,t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState AS BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM tWfSendBack t1 inner join tWorkFormBase t2 on t1.WfId=t2.PkId 
        inner join tTelGetDeal t3 on t2.GetDealPK=t3.PkId left join tWfJudge t4 on t4.WfId = t1.WfId 
        WHERE t1.SDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 and t2.IsDel=0 
'''
        if(parameter?.workFormNo)
            sql += " and t2.workFormNO = #{workFormNO} "
        if(parameter?.wsCode)
            sql += " and t2.WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " and t2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and t2.WsTopic = #{fromTopic} "
        if(parameter?.sdeptName)
            sql += " and t1.sdeptName = #{sdeptName} "
    return sql
}

// 查询本单位信件工单导出
def mailWFDeptListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel, 
        t3.SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,t1.GetTime signTime,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos, 
        SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM mWfSendBack t1 INNER JOIN mMailWorkForm t2 on t1.WfId=t2.PkId 
        inner join mMailGetDeal t3 on t2.OriginFK=t3.PkId left join mWfJudge t4 on t4.WfId = t1.WfId 
        WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 AND t2.IsDel=0 
'''
        if(parameter?.workFormNO)
            sql += " and WorkFormNO = #{workFormNO} "
        if(parameter?.wsCode)
            sql += " and WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " and t2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and FromTopic = #{fromTopic} "
        if(parameter?.sbeptName)
            sql += " and sdeptName = #{sdeptName} "
    return sql
}

// 查询本单位信件工单不满意件导出
def mailWFDissatisfyListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel, 
        t3.SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM mWfSendBack t1 INNER JOIN mMailWorkForm t2 on t1.WfId=t2.PkId 
        inner join mMailGetDeal t3 on t2.OriginFK=t3.PkId left join mWfJudge t4 on t4.WfId = t1.WfId 
        WHERE SBDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 AND t2.IsDel=0 AND t4.JudgeC='1' 
'''
    if(parameter?.workFormNO)
        sql += " and WorkFormNO = #{workFormNO} "
    if(parameter?.wsCode)
        sql += " and WsCode = #{wsCode} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
    if(parameter?.dcntAcode)
        sql += " and t2.DCntACode = #{dcntAcode} "
    if(parameter?.fromTopic)
        sql += " and FromTopic = #{fromTopic} "
    if(parameter?.sbeptName)
        sql += " and sdeptName = #{sdeptName} "
    return sql
}

// 查询下级单位信件工单导出
def subDeptMailListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel, 
        t3.SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM mWfSendBack t1 INNER JOIN mMailWorkForm t2 on t1.WfId=t2.PkId left join mWfJudge t4 on t4.WfId = t1.WfId 
        inner join mMailGetDeal t3 on t2.OriginFK=t3.PkId 
        WHERE t1.SDeptID=#{param1} AND SendState IN(21,22,23,40) AND t1.IsDel=0 AND t2.IsDel=0 
'''
        if(parameter?.workFormNO)
            sql += " and WorkFormNO = #{workFormNO} "
        if(parameter?.wsCode)
            sql += " and WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " t2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and FromTopic = #{fromTopic} "
        if(parameter?.sdeptName)
            sql += " and sdeptName = #{sdeptName} "
    return sql
}

// 查询下级单位电话工单导出
def telUrgeWFListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId pkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel, 
        '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        t1.SDeptName,t1.SeqName,t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState AS BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId 
        left join tTelGetDeal t3 on t2.GetDealPK=t3.PkId left join tWfJudge t4 on t4.WfId = t1.WfId 
        where t1.SDeptID=#{param1} and t1.SendState in (10,21,22,23) 
        and t1.BackState in (0,10,12) and t1.IsDel=0
'''
        if(parameter?.workFormNO)
            sql += " and (select WorkFormNO from tWorkFormBase where PkId=WfId) = #{workFormNO} "
        if(parameter?.sbDeptName)
            sql += " and SDeptName = #{tel.sdeptName}]] [[and SBDeptName = #{sbDeptName} "
        if(parameter?.signBeginTime)
            sql += " and convert(varchar(10),GetTime,120) >= #{signBeginTime} "
        if(parameter?.signEndTime)
            sql += " and convert(varchar(10),GetTime,120) <= #{signEndTime} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),BackLimit,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),BackLimit,120) <= #{endTime} "
    return sql
}

// 查询下级办理中信件工单导出
def subUrgeMailListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId pkId,t1.WfId,t2.FmContent,t1.MailPK,t2.WorkFormNO,t2.FromTopic,t2.FromName,t2.FromTel, 
        t3.SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        SDeptName,SeqName,SortName,DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM mWfSendBack t1 INNER JOIN mMailWorkForm t2 on t1.WfId=t2.PkId left join mWfJudge t4 on t4.WfId = t1.WfId 
        inner join mMailGetDeal t3 on t2.OriginFK=t3.PkId 
        WHERE t1.SDeptID=#{param1} and t1.SendState in (10,21,22,23) and t1.BackState in (0,10,12,22) and t1.IsDel=0
'''
        if(parameter?.workFormNO)
            sql += " and dbo.frMailWFNo(WfId) = #{workFormNO} "
        if(parameter?.sbDeptName)
            sql += " and SBDeptName = #{sbDeptName} "
        if(parameter?.spareString)
            sql += " and dbo.frMailWFSpareStr(WfId) = #{spareString} "
        if(parameter?.sdeptName)
            sql += " and SDeptName = #{sdeptName} "
        if(parameter?.signBeginTime)
            sql += " and convert(varchar(10),GetTime,120) >= #{signBeginTime} "
        if(parameter?.signEndTime)
            sql += " and convert(varchar(10),GetTime,120) <= #{signEndTime} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),BackLimit,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),BackLimit,120) <= #{endTime} "
    return sql
}

// 查询本单位电话工单不满意件导出
def tWFDeptListExport(Object parameter){
    def sql = '''
        SELECT t1.PkId,t1.WfId,t2.FmContent,t1.GetDealPK AS telId,t2.WorkFormNO,t2.WsTopic FromTopic,t2.FromName,t2.FromTelA FromTel, 
        '12345' SpareString,t3.FmAddress,t3.FromTime,t3.DFmClaName,SBDeptName,BackDesc, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,4)) dCntAName1, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,6)) dCntAName2, 
        (select SortName from bSort where CodePk=left(t3.DCntACode,8)) dCntAName3, 
        iif(t4.JudgeC='',0,t4.JudgeC) JudgeC,iif(t4.DeptPos='',0,t4.DeptPos) DeptPos,t1.GetTime signTime, 
        t1.SDeptName,t1.SeqName,t1.SortName,t1.DealName,CONVERT(VARCHAR(10),BackLimit,20) AS BackLimit, 
        BackTime,BackState AS BackState,BackRemark, 
        case when t1.SendState in(10,11) then '0' 
        when t1.SendState in(21,23) and t1.BackState in(0,12,22) then '2' 
        when t1.BackState = 10 then '3' when t1.SendState  = 40 then '4' 
        when t1.SendState not in(30,40) and t1.BackState in(11,21) then '10' 
        when t1.SendState in (22,23) then '1' else '-1' end as currentState 
        FROM tWfSendBack t1 inner join tWorkFormBase t2 on t1.WfId=t2.PkId 
        inner join tTelGetDeal t3 on t2.GetDealPK=t3.PkId left join tWfJudge t4 on t4.WfId = t1.WfId 
        WHERE t1.SBDeptID=#{param1} AND t1.SendState IN(21,22,23,40) AND t1.IsDel=0 and t2.IsDel=0 and t4.JudgeC ='1' 
'''
        if(parameter?.workFormNO)
            sql += " and t2.workFormNO = #{workFormNO} "
        if(parameter?.wsCode)
            sql += " and t2.WsCode = #{wsCode} "
        if(parameter?.beginTime)
            sql += " and convert(varchar(10),t2.IptTime,120) >= #{beginTime} "
        if(parameter?.endTime)
            sql += " and convert(varchar(10),t2.IptTime,120) <= #{endTime} "
        if(parameter?.dcntAcode)
            sql += " and t2.DCntACode = #{dcntAcode} "
        if(parameter?.fromTopic)
            sql += " and t2.WsTopic = #{fromTopic} "
        if(parameter?.sdeptName)
            sql += " and t1.sdeptName = #{sdeptName} "
    return sql
}

// jjq
// 查找待本部门回复的电话工单导出
def muReplyWaitJson_jjqExport(Object parameter){
    def sql = '''
         select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,isUrge,
            t2.WorkFormNO as myTwfNO,t2.WsTopic as myTwfTopic,t2.fmContent as FmContent,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,4),4)) dCntAName1,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,6),6)) dCntAName2,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,8),8)) dCntAName3,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,SDeptName,convert(varchar(10),GetTime,20) as acDate,
            BackState as bkState,BackRemark as acRemark,t1.PkId as opDeal,BackDesc as backDesc,subBackState cbackDesc 
            from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
            where t1.SBDeptID=#{param1} and t1.SendState in(21,23) and t1.BackState in (0,12,22) and t1.IsDel=0 and t2.IsDel=0 
    '''
    if (parameter?.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter?.backDesc)
        sql += " and t1.subBackState=#{backDesc} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) <= #{endTime} "
    if(parameter?.sdeptName)
        sql += " and t1.SDeptName =#{sdeptName} "
    if(parameter?.formType)
        sql += " and t1.isUrge = #{formType} "
    return sql
}

// px
// 查找待本部门回复的电话工单导出
def muReplyWaitJson_pxExport(Object parameter){
    def sql = '''
         select t1.PkId as myPkVal,WfId,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,isUrge,
            t2.WorkFormNO as myTwfNO,t2.WsTopic as myTwfTopic,t2.fmContent as FmContent,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,4),4)) dCntAName1,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,6),6)) dCntAName2,
            (select SortName from bSort where CodePk=str(left(t3.DCntACode,8),8)) dCntAName3,
            SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,SDeptName,convert(varchar(10),GetTime,20) as acDate,
            BackState as bkState,BackRemark as acRemark,t1.PkId as opDeal,BackDesc as backDesc,dbo.tWfNOBackDesc(WfId,#{param1}) cbackDesc 
            from tWfSendBack t1 left join tWorkFormBase t2 on t1.WfId=t2.PkId left join tTelGetDeal t3 on t1.GetDealPk = t3.PkId 
            where t1.SBDeptID=#{param1} and t1.SendState in(21,23) and t1.BackState in (0,12,22) and t1.IsDel=0 and t2.IsDel=0 
    '''
    if (parameter?.workFormNO)
        sql += " and t2.WorkFormNO = #{workFormNO} "
    if (parameter?.backDesc)
        sql += " and #{backDesc} = dbo.tWfNOBackDesc(WfId,#{param1}) "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),t1.BackLimit,120) <= #{endTime} "
    if(parameter?.sdeptName)
        sql += " and t1.SDeptName =#{sdeptName} "
    if(parameter?.formType)
        sql += " and t1.isUrge = #{formType} "
    return sql
}

// jjq
// 查找待本部门回复的信件工单导出
def replyWaitJson_jjqExport(Object parameter){
    def sql = '''
        select m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,isUrge,
        m2.FromTopic as myMwfTopic,m2.FmContent as FmContent,SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,4),4)) dCntAName1, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,6),6)) dCntAName2, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,8),8)) dCntAName3, 
        SDeptName,convert(varchar(10),GetTime,20) as acDate,BackState as bkState,BackRemark as acRemark,
        subBackState cbackDesc,m2.SpareString as SpareString 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId=m2.PkId left join mMailGetDeal m3 on m2.OriginFK=m3.PkId 
        where m1.SBDeptID = #{param1} and m1.SendState in(21,23) and m1.BackState in (0,12,22) and m1.IsDel=0 and m2.IsDel=0 
    '''
    if (parameter?.sdeptName)
        sql += " and m1.SDeptName =#{sdeptName} "
    if (parameter?.spareString)
        sql += " and m2.SpareString =#{spareString} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) <= #{endTime} "
    if(parameter?.backDesc)
        sql += " and m1.subBackState=#{backDesc} "
    if(parameter?.workFormNO)
        sql += " and m2.WorkFormNO = #{workFormNO} "
    if(parameter?.formType)
        sql += " and m1.isUrge = #{formType} "
    return sql
}

// px
// 查找待本部门回复的信件工单导出
def replyWaitJson_pxExport(Object parameter){
    def sql = '''
        select m1.PkId as myPkVal,WfId,MailPK,OriLevel,SDeptID as fmDept,SBDeptID as sbDept,m2.WorkFormNO as myMwfNO,isUrge,
        m2.FromTopic as myMwfTopic,m2.FmContent as FmContent,SeqName,SBDeptName,SortName,DealName,convert(varchar(10),BackLimit,20) as bkLimit,
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,4),4)) dCntAName1, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,6),6)) dCntAName2, 
        (select SortName from bSort where CodePk=str(left(m3.DCntACode,8),8)) dCntAName3, 
        SDeptName,convert(varchar(10),GetTime,20) as acDate,BackState as bkState,BackRemark as acRemark,
        dbo.mWfNOBackDesc(WfId,#{param1}) cbackDesc,m2.SpareString as SpareString 
        from mWfSendBack m1 left join mMailWorkForm m2 on m1.WfId=m2.PkId left join mMailGetDeal m3 on m2.OriginFK=m3.PkId 
        where m1.SBDeptID = #{param1} and m1.SendState in(21,23) and m1.BackState in (0,12,22) and m1.IsDel=0 and m2.IsDel=0 
    '''
    if (parameter?.sdeptName)
        sql += " and m1.SDeptName =#{sdeptName} "
    if (parameter?.spareString)
        sql += " and m2.SpareString =#{spareString} "
    if(parameter?.beginTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) >= #{beginTime} "
    if(parameter?.endTime)
        sql += " and convert(varchar(10),m1.BackLimit,120) <= #{endTime} "
    if(parameter?.backDesc)
        sql += " and replace(#{backDesc},'0','') = dbo.mWfNOBackDesc(WfId,#{param1}) "
    if(parameter?.workFormNO)
        sql += " and m2.WorkFormNO = #{workFormNO} "
    if(parameter?.formType)
        sql += " and m1.isUrge = #{formType} "
    return sql
}